"""数据库表结构修复脚本

修复BrainWave表的结构问题：
1. 删除冗余的session_id字段及其唯一约束
2. 将id字段从varchar改为自增整型
3. 确保表结构与模型定义一致

使用方法：
python fix_database_schema.py
"""

import sys
import logging
import pymysql
from app.core.database import database

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s | %(levelname)s | %(message)s')
logger = logging.getLogger(__name__)


def fix_brain_wave_table():
    """修复BrainWave表结构"""
    try:
        # 连接数据库
        database.connect()
        logger.info("连接到MySQL数据库成功")
        
        # 获取原始连接
        connection = database.connection()
        cursor = connection.cursor()
        
        # 1. 检查并删除session_id字段
        logger.info("检查session_id字段...")
        cursor.execute("""
            SELECT COLUMN_NAME 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = 'brain_wave_db' 
            AND TABLE_NAME = 'brain_wave' 
            AND COLUMN_NAME = 'session_id'
        """)
        
        if cursor.fetchone():
            logger.info("删除session_id字段及其约束...")
            # 先删除唯一约束
            cursor.execute("ALTER TABLE brain_wave DROP INDEX session_id_unique")
            # 再删除字段
            cursor.execute("ALTER TABLE brain_wave DROP COLUMN session_id")
            logger.info("session_id字段删除成功")
        else:
            logger.info("session_id字段不存在，跳过删除")
        
        # 2. 修改id字段为自增整型
        logger.info("修改id字段为自增整型...")
        
        # 首先检查当前id字段的类型
        cursor.execute("""
            SELECT DATA_TYPE, COLUMN_KEY 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = 'brain_wave_db' 
            AND TABLE_NAME = 'brain_wave' 
            AND COLUMN_NAME = 'id'
        """)
        
        result = cursor.fetchone()
        if result and result[0] == 'varchar':
            logger.info("当前id字段为varchar类型，需要修改...")
            
            # 清空表数据（因为varchar无法直接转换为自增整型）
            cursor.execute("DELETE FROM brain_wave")
            logger.info("清空表数据以便修改字段类型")
            
            # 删除主键约束
            cursor.execute("ALTER TABLE brain_wave DROP PRIMARY KEY")
            
            # 修改id字段为自增整型
            cursor.execute("ALTER TABLE brain_wave MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
            logger.info("id字段修改为自增整型成功")
        else:
            logger.info("id字段已经是正确类型，跳过修改")
        
        # 提交更改
        connection.commit()
        logger.info("数据库表结构修复完成")
        
    except Exception as e:
        logger.error(f"修复数据库表结构失败: {e}")
        if 'connection' in locals():
            connection.rollback()
        raise
    finally:
        if database.is_connection_usable():
            database.close()


if __name__ == "__main__":
    try:
        fix_brain_wave_table()
        print("数据库表结构修复成功！")
    except Exception as e:
        print(f"修复失败: {e}")
        sys.exit(1)